package top.wuyang.study.java24.dao;

import top.wuyang.study.java24.entity.TbDept;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TbDeptDAO {

    public static final String INSERT = "insert into tb_dept(dept_name,description) values(?,?)";
    public static final String QUERY_BY_ID = "select id,dept_name,description,create_time from tb_dept where id=?";
    public static final String QUERY_BY_NAME = "select * from tb_dept where dept_name=?";
    public static final String QUERY = "select id,dept_name,description,create_time from tb_dept";
    public static final String DELETE = "delete from tb_dept where id=?";
    public static final String QUERY_COUNT_BY_ID = "select count(*) from tb_employee where dept_id = ?";


    public static int queryCountById(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_COUNT_BY_ID);
        ps.setInt(1, id);
        int count = 0;
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            count = rs.getInt(1);
        }
        conn.close();
        return count;
    }

    public static int delete(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(DELETE);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

    public static int insert(TbDept dept) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(INSERT);
        ps.setString(1, dept.getDept_name());
        ps.setString(2, dept.getDescription());
        int i = ps.executeUpdate();
        conn.close();
        return i;
    }

    public static TbDept queryById(Integer id) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_BY_ID);
        ps.setInt(1, id);
        TbDept dept = null;
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            dept = new TbDept();
            dept.setId(rs.getInt("id"));
            dept.setDept_name(rs.getString("dept_name"));
            dept.setDescription(rs.getString("description"));
            dept.setCreate_time(rs.getTimestamp("create_time"));
        }
        conn.close();
        return dept;
    }

    public static TbDept queryByName(String name) throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY_BY_NAME);
        ps.setString(1, name);
        TbDept dept = null;
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            dept = new TbDept();
            dept.setId(rs.getInt("id"));
            dept.setDept_name(rs.getString("dept_name"));
            dept.setDescription(rs.getString("description"));
            dept.setCreate_time(rs.getTimestamp("create_time"));
        }
        conn.close();
        return dept;
    }

    public static List<TbDept> query() throws Exception {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = conn.prepareStatement(QUERY);
        List<TbDept> list = new ArrayList<>();
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            TbDept dept = new TbDept();
            dept.setId(rs.getInt("id"));
            dept.setDept_name(rs.getString("dept_name"));
            dept.setDescription(rs.getString("description"));
            dept.setCreate_time(rs.getTimestamp("create_time"));
            list.add(dept);
        }
        conn.close();
        return list;
    }

}
